package com.mitake.utils;

import java.awt.Color;
import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.util.Iterator;

import javax.servlet.ServletContext;
import javax.servlet.http.HttpServletResponse;

import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Font;
import org.json.JSONArray;
import org.json.JSONObject;

public class ExcelUtil { 
 
	public static String processExcel(JSONArray jArray,ServletContext servletContext) throws IOException{
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFCellStyle cs = wb.createCellStyle();
		HSSFRow row0 = sheet.createRow(0);
		HSSFRow row1 = sheet.createRow(1);
		cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);    
		cs.setFillForegroundColor(HSSFColor.RED.index);   
		int n = 2;
		Cell cell = null;
		for (int i = 0; i < jArray.length(); i++) {
			JSONObject json = (JSONObject) jArray.get(i);
			Iterator<String> sIterator = json.keys();  
			while(sIterator.hasNext()){  
			    // 获得key  
			    String key = sIterator.next();  
			    // 根据key获得value, value也可以是JSONObject,JSONArray,使用对应的参数接收即可  
			    JSONArray value = json.getJSONArray(key);  
			    int m = 0;
			    HSSFRow row = sheet.createRow(n);
			    for(int j=0; j<value.length(); j++){
			    	JSONObject jo = new JSONObject();
			    	jo = (JSONObject) value.get(j);
//			    	Cell cell = row0.createCell(m);
//			    	cell.setCellValue(jo.getString("host"));
//			    	cell.setCellStyle(cs);
			    	row0.createCell(m).setCellValue(jo.getString("host"));
//			    	sheet.addMergedRegion(new CellRangeAddress(0, 0, m, m+7));
			    	
			    	row1.createCell(m).setCellValue("日期");
			    	row1.createCell(m+1).setCellValue("时间");
			    	row1.createCell(m+2).setCellValue("开");
			    	row1.createCell(m+3).setCellValue("高");
			    	row1.createCell(m+4).setCellValue("低");
			    	row1.createCell(m+5).setCellValue("收");
			    	row1.createCell(m+6).setCellValue("量");
			    	row1.createCell(m+7).setCellValue("参考价");
			    	
			    	row.createCell(m).setCellValue(jo.getString("date"));
			    	row.createCell(m+1).setCellValue(jo.getString("time"));
			    	
			    	cell = row.createCell(m+2);
			    	cell.setCellValue(jo.getString("firstPrice"));
			    	if("1".equals(jo.getString("firstPrice_flag"))){
			    		cell.setCellStyle(cs);
			    	}
			    	
			    	cell = row.createCell(m+3);
			    	cell.setCellValue(jo.getString("highPrice"));
			    	if("1".equals(jo.getString("highPrice_flag"))){
			    		cell.setCellStyle(cs);
			    	}
			    	
			    	cell = row.createCell(m+4);
			    	cell.setCellValue(jo.getString("lowPrice"));
			    	if("1".equals(jo.getString("lowPrice_flag"))){
			    		cell.setCellStyle(cs);
			    	}
			    	
			    	cell = row.createCell(m+5);
			    	cell.setCellValue(jo.getString("lastPrice"));
			    	if("1".equals(jo.getString("lastPrice_flag"))){
			    		cell.setCellStyle(cs);
			    	}
			    	
			    	cell = row.createCell(m+6);
			    	cell.setCellValue(jo.getString("volume"));
			    	if("1".equals(jo.getString("volume_flag"))){
			    		cell.setCellStyle(cs);
			    	}
			    	
			    	cell = row.createCell(m+7);
			    	cell.setCellValue(jo.getString("reference"));
			    	if("1".equals(jo.getString("reference_flag"))){
			    		cell.setCellStyle(cs);
			    	}
			    	m=m+8;
			    }
			    
			}  
			n++;
		}
		String filename = System.currentTimeMillis()+"";
		String excelPath = servletContext.getRealPath("/excel/")+"\\"+filename+".xlsx";
		File f =new File(excelPath);
		wb.write(f);
		
		wb.close();
		return excelPath;
	}
	
	public static String processExcelDay5(JSONArray jArray,ServletContext servletContext) throws IOException{
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFCellStyle cs = wb.createCellStyle();
		HSSFRow row0 = sheet.createRow(0);
		HSSFRow row1 = sheet.createRow(1);
		
		cs.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);    
		cs.setFillForegroundColor(HSSFColor.RED.index);  
		
		int n = 2;
		Cell cell = null;
		for (int i = 0; i < jArray.length(); i++) {
			JSONObject json = (JSONObject) jArray.get(i);
			Iterator<String> sIterator = json.keys();  
			while(sIterator.hasNext()){  
			    // 获得key  
			    String key = sIterator.next();  
			    // 根据key获得value, value也可以是JSONObject,JSONArray,使用对应的参数接收即可  
			    JSONArray value = json.getJSONArray(key);  
			    int m = 0;
			    HSSFRow row = sheet.createRow(n);
			    for(int j=0; j<value.length(); j++){
			    	JSONObject jo = new JSONObject();
			    	jo = (JSONObject) value.get(j);
			    	row0.createCell(m).setCellValue(jo.getString("host"));
//			    	sheet.addMergedRegion(new CellRangeAddress(0, 0, m, m+7));
			    	
			    	row1.createCell(m).setCellValue("日期");
			    	row1.createCell(m+1).setCellValue("收");
			    	row1.createCell(m+2).setCellValue("量");
			    	
			    	row.createCell(m).setCellValue(jo.getString("datetime"));
			    	
			    	cell = row.createCell(m+1);
			    	cell.setCellValue(jo.getString("lastPrice"));
			    	if("1".equals(jo.getString("lastPrice_flag"))){
			    		cell.setCellStyle(cs);
			    	}
			    	
			    	cell = row.createCell(m+2);
			    	cell.setCellValue(jo.getString("volume"));
			    	if("1".equals(jo.getString("volume_flag"))){
			    		cell.setCellStyle(cs);
			    	}
			    	
			    	m=m+3;
			    }
			    
			}  
			n++;
		}
		String filename = System.currentTimeMillis()+"";
		String excelPath = servletContext.getRealPath("/excel/")+"\\"+filename+".xlsx";
		File f =new File(excelPath);
		wb.write(f);
		
		wb.close();
		return excelPath;
	}
	
	public static String processExcelQuote(JSONArray jArray,ServletContext servletContext) throws IOException{
		HSSFWorkbook wb = new HSSFWorkbook();
		HSSFSheet sheet = wb.createSheet();
		HSSFCellStyle cs = wb.createCellStyle();

		HSSFRow row0 = sheet.createRow(0);
		HSSFRow row1 = sheet.createRow(1);
		

		row0.createCell(0).setCellValue("机房");
		row0.createCell(1).setCellValue("代码");
		row0.createCell(2).setCellValue("名称");
		row0.createCell(3).setCellValue("交易时间");
		row0.createCell(4).setCellValue("市场类别");
		row0.createCell(5).setCellValue("次类别");
		row0.createCell(6).setCellValue("最新价");
		row0.createCell(7).setCellValue("最高价");
		row0.createCell(8).setCellValue("最低价");
		row0.createCell(9).setCellValue("今开价");
		row0.createCell(10).setCellValue("昨收价");
		row0.createCell(11).setCellValue("涨跌比率");
		row0.createCell(12).setCellValue("总量");
		row0.createCell(13).setCellValue("当前成交量");
		row0.createCell(14).setCellValue("换手率");
		row0.createCell(15).setCellValue("涨停价");
		row0.createCell(16).setCellValue("跌停价");
		row0.createCell(17).setCellValue("均价");
		row0.createCell(18).setCellValue("涨跌");
		row0.createCell(19).setCellValue("成交金额");
		row0.createCell(20).setCellValue("量比");
		row0.createCell(21).setCellValue("买一价");
		row0.createCell(22).setCellValue("卖一价");
		row0.createCell(23).setCellValue("外盘量");
		row0.createCell(24).setCellValue("内盘量");
		row0.createCell(25).setCellValue("总值");
		row0.createCell(26).setCellValue("流值");
		row0.createCell(27).setCellValue("净资产");
		row0.createCell(28).setCellValue("PE(市盈)");
		row0.createCell(29).setCellValue("ROE(净资产收益率)");
		row0.createCell(30).setCellValue("总股本");
		row0.createCell(31).setCellValue("流通股");
		row0.createCell(32).setCellValue("五档买价");
		row0.createCell(33).setCellValue("五档卖价");
		row0.createCell(34).setCellValue("五档买量");
		row0.createCell(35).setCellValue("五档卖量");
		row0.createCell(36).setCellValue("五档买单量");
		row0.createCell(37).setCellValue("五档卖单量");
		row0.createCell(38).setCellValue("振幅比率");
		row0.createCell(39).setCellValue("收益");
		row0.createCell(40).setCellValue("交易明细");
		row0.createCell(41).setCellValue("上涨家数");
		row0.createCell(42).setCellValue("平盘家数");
		row0.createCell(43).setCellValue("下跌家数");
		
		int n = 1;
		Cell cell = null;
		for (int i = 0; i < jArray.length(); i++) {
			JSONObject json = (JSONObject) jArray.get(i);
			json.get("netAsset");
			Iterator<String> sIterator = json.keys();  
			HSSFRow row = sheet.createRow(n);
			JSONObject borderJson = json.getJSONObject("border");		
			String bottom = borderJson.getString("d");
			String top =  borderJson.getString("v");
			
			while(sIterator.hasNext()){  
			    // 获得key  
			    String key = sIterator.next();  
			    // 根据key获得value, value也可以是JSONObject,JSONArray,使用对应的参数接收即可  
			    JSONObject value = json.getJSONObject(key);  
			    String v = value.getString("v");
			    String d = value.getString("d");

			    if("netAsset".equals(key)){
			    	cell = row.createCell(27);
			    	cell.setCellValue(v);
			    	
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    	
			    }
			    if("amplitudeRate".equals(key)){
			    	cell = row.createCell(38);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("upCount".equals(key)){
			    	cell = row.createCell(41);
			    	cell.setCellValue(v);
			    	
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("openPrice".equals(key)){
			    	cell = row.createCell(9);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("roe".equals(key)){
			    	cell = row.createCell(29);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("sellPrice".equals(key)){
			    	cell = row.createCell(22);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("limitDown".equals(key)){
			    	cell = row.createCell(16);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("volumeRatio".equals(key)){
			    	cell = row.createCell(20);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("buyPricesTmp".equals(key)){
			    	cell = row.createCell(32);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("nowVolume".equals(key)){
			    	cell = row.createCell(13);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("sellPricesTmp".equals(key)){
			    	cell = row.createCell(33);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("datetime".equals(key)){
			    	cell = row.createCell(3);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("subtype".equals(key)){
			    	cell = row.createCell(5);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("lowPrice".equals(key)){
			    	cell =row.createCell(8);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("host".equals(key)){
			    	cell = row.createCell(0);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("highPrice".equals(key)){
			    	cell = row.createCell(7);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("id".equals(key)){
			    	cell = row.createCell(1);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("changeRate".equals(key)){
			    	cell = row.createCell(11);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("sameCount".equals(key)){
			    	cell = row.createCell(42);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("buyPrice".equals(key)){
			    	cell = row.createCell(21);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("totalValue".equals(key)){
			    	cell = row.createCell(25);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("sellVolume".equals(key)){
			    	cell = row.createCell(24);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("amount".equals(key)){
			    	cell = row.createCell(19);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("buyVolumesTmp".equals(key)){
			    	cell = row.createCell(34);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("sellVolumesTmp".equals(key)){
			    	cell = row.createCell(35);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("change".equals(key)){
			    	cell = row.createCell(18);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("sellSingleVolumesTmp".equals(key)){
			    	cell = row.createCell(37);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("limitUP".equals(key)){
			    	cell = row.createCell(15);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("tradeTickTmp".equals(key)){
			    	cell = row.createCell(40);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("market".equals(key)){
			    	cell = row.createCell(4);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("volume".equals(key)){
			    	cell = row.createCell(12);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("averageValue".equals(key)){
			    	cell = row.createCell(17);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("receipts".equals(key)){
			    	cell = row.createCell(39);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("preClosePrice".equals(key)){
			    	 cell =row.createCell(10);
			    	 cell.setCellValue(v);
				    	HSSFCellStyle cellStyle =  wb.createCellStyle();
						if(top.equals("1")){
							cellStyle.setBorderTop(BorderStyle.THIN);
							cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
						}else if(bottom.equals("1")){
							cellStyle.setBorderBottom(BorderStyle.THIN);
							cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
						}
				    	if("Y".equals(d)) {
				    		Font font = wb.createFont();    
				    		font.setColor(HSSFColor.RED.index);
				    		cellStyle.setFont(font);
				    	}
				    	cell.setCellStyle(cellStyle);
			    }
			    if("downCount".equals(key)){
			    	cell = row.createCell(43);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("flowValue".equals(key)){
			    	cell = row.createCell(26);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("buySingleVolumesTmp".equals(key)){
			    	cell = row.createCell(36);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("capitalization".equals(key)){
			    	cell = row.createCell(30);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("pe".equals(key)){
			    	cell = row.createCell(28);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("circulatingShares".equals(key)){
			    	cell = row.createCell(31);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("name".equals(key)){
			    	cell = row.createCell(2);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("turnoverRate".equals(key)){
			    	cell = row.createCell(14);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("buyVolume".equals(key)){
			    	cell = row.createCell(23);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			    if("lastPrice".equals(key)){
			    	cell = row.createCell(6);
			    	cell.setCellValue(v);
			    	HSSFCellStyle cellStyle =  wb.createCellStyle();
					if(top.equals("1")){
						cellStyle.setBorderTop(BorderStyle.THIN);
						cellStyle.setTopBorderColor(HSSFColor.BLACK.index);
					}else if(bottom.equals("1")){
						cellStyle.setBorderBottom(BorderStyle.THIN);
						cellStyle.setBottomBorderColor(HSSFColor.BLACK.index);
					}
			    	if("Y".equals(d)) {
			    		Font font = wb.createFont();    
			    		font.setColor(HSSFColor.RED.index);
			    		cellStyle.setFont(font);
			    	}
			    	cell.setCellStyle(cellStyle);
			    }
			}
			n++;
		}
		String filename = System.currentTimeMillis()+"";
		String excelPath = servletContext.getRealPath("/excel/")+"\\"+filename+".xlsx";
		File f =new File(excelPath);
		wb.write(f);
		
		wb.close();
		return excelPath;
	}

}
